package com.csi.dao.impl;

import com.csi.dao.EmployeeDao;
import com.csi.domain.Permission;
import com.csi.domain.Role;
import com.csi.domain.User;
import com.csi.pojo.PageBean;
import com.csi.domain.Employee;
import com.csi.utils.DruidUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class EmployeeDaoImpl implements EmployeeDao {

    @Override
    public String findLast() throws SQLException {
        Connection connection = DruidUtils.getConnection();

        /*
        使用 LIKE 条件筛选以 "E" 开头的员工号。
        然后，使用 SUBSTRING() 函数截取 "E" 后面的数字部分，并使用 CAST() 函数将其转换为无符号整数。
        最后，按照转换后的数字降序排序，并使用 LIMIT 1 限制结果集只返回一行，即最后一位员工的员工号。
        */
        String sql = "SELECT employee_account FROM employee WHERE employee_account LIKE 'E%' ORDER BY CAST(SUBSTRING(employee_account, 2) AS UNSIGNED) DESC LIMIT 1; ";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        String str = null;
        if (rs.next()) {
            str = rs.getString("employee_account");
        }

        DruidUtils.close(connection);

        return str;
    }

    @Override
    public PageBean<Employee> findPage(int currentPageNumber) throws SQLException {
        Connection connection = DruidUtils.getConnection();

        PageBean<Employee> pageBean = new PageBean<>();
        pageBean.setCurrentPageNumber(currentPageNumber);

        String sql = """
        SELECT
                       u.user_id,
                       u.user_number,
                       u.user_name,
                       u.english_name,
                       u.login_account,
                       u.gender,
                       u.home_address,
                       u.phone,
                       u.email,
                       u.borndate,
                       u.image_address,
                       u.company_address,
                       u.status,
                       e.employee_id,
                       e.employee_account,
                       e.onboarding_date,
                       e.resign_date,
                       r.role_name,
                       r.role_id
                   FROM user u
                   JOIN user_employee ue ON u.user_id = ue.user_id
                   JOIN employee e ON ue.employee_id = e.employee_id
                   JOIN user_role ur ON u.user_id = ur.user_id
                   JOIN role r ON ur.role_id = r.role_id
                   WHERE r.role_id != 6
                   ORDER BY e.employee_id ASC, r.role_id ASC;
        """;

        PreparedStatement ps = connection.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        List<Employee> list = new ArrayList<>(); // 创建一个空的List集合，用于存储所有员工信息

        Map<Integer, Employee> employeeMap = new HashMap<>(); // 创建一个空的Map集合，用于存储员工信息

        while (rs.next()) {
            int employeeId = rs.getInt("employee_id");

            if (!employeeMap.containsKey(employeeId)) { // 如果Map集合中不存在该员工信息，则创建一个新的员工对象
                Employee employee = new Employee();

                employee.setUserId(rs.getInt("user_id"));
                employee.setUserNumber(rs.getString("user_number"));
                employee.setUserName(rs.getString("user_name"));
                employee.setEnglishName(rs.getString("english_name"));
                employee.setLoginAccount(rs.getString("login_account"));
                employee.setGender(rs.getString("gender"));
                employee.setHomeAddress(rs.getString("home_address"));
                employee.setPhone(rs.getString("phone"));
                employee.setEmail(rs.getString("email"));
                employee.setBorndate(rs.getDate("borndate"));
                employee.setImageAddress(rs.getString("image_address"));
                employee.setCompanyAddress(rs.getString("company_address"));
                employee.setStatus(rs.getInt("status"));
                employee.setEmployeeId(employeeId);
                employee.setOnboardingDate(rs.getDate("onboarding_date"));
                employee.setResignDate(rs.getDate("resign_date"));
                employee.setEmployeeAccount(rs.getString("employee_account"));

                List<Role> roles = new ArrayList<>(); // 创建一个空的集合，用于存储员工的所有角色信息

                Role role = new Role();
                role.setRoleName(rs.getString("role_name"));
                role.setRoleId(rs.getInt("role_id"));
                roles.add(role); // 将角色信息添加到集合中

                employee.setRoles(roles); // 将所有角色信息设置到员工对象中

                employeeMap.put(employeeId, employee); // 将员工对象添加到Map集合中
            } else { // 如果Map集合中已经存在该员工信息，则将角色信息添加到员工对象中
                Employee employee = employeeMap.get(employeeId);

                List<Role> roles = employee.getRoles();

                Role role = new Role();
                role.setRoleName(rs.getString("role_name"));
                role.setRoleId(rs.getInt("role_id"));
                roles.add(role); // 将角色信息添加到集合中

                employee.setRoles(roles); // 将所有角色信息设置到员工对象中

                employeeMap.put(employeeId, employee); // 将员工对象添加到Map集合中
            }
        }

        list.addAll(employeeMap.values()); // 将Map集合中的所有员工对象添加到List集合中

        int startIndex = (currentPageNumber - 1) * pageBean.getPerPageNumber(); // 起始索引
        int endIndex = Math.min(startIndex + pageBean.getPerPageNumber(), list.size()); // 结束索引

        pageBean.setDatas(list.subList(startIndex, endIndex)); // 设置当前页的数据

        pageBean.setTotalRecords(list.size()); // 总记录数

        DruidUtils.close(connection);

        return pageBean;
    }

    @Override
    public void add(User user, Employee employee) throws SQLException {
        Connection conn = DruidUtils.getConnection();
        // 向user表中插入数据
        String sql1 = "INSERT INTO user (user_number, user_name, english_name, login_account, gender, phone, email, borndate,status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1)";
        PreparedStatement pstmt1 = conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
        pstmt1.setString(1, user.getUserNumber());
        pstmt1.setString(2, user.getUserName());
        pstmt1.setString(3, user.getEnglishName());
        pstmt1.setString(4, user.getLoginAccount());
        pstmt1.setString(5, user.getGender());
        pstmt1.setString(6, user.getPhone());
        pstmt1.setString(7, user.getEmail());
        pstmt1.setDate(8, new Date(user.getBorndate().getTime()));

        pstmt1.executeUpdate();
        ResultSet rs1 = pstmt1.getGeneratedKeys();
        int userId = 0;
        if (rs1.next()) {
            userId = rs1.getInt(1);
        }

        // 向employee表中插入数据
        String sql2 = "INSERT INTO employee (employee_account, onboarding_date) VALUES (?, ?)";
        PreparedStatement pstmt2 = conn.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS);
        pstmt2.setString(1, employee.getEmployeeAccount());
        pstmt2.setDate(2, new java.sql.Date(employee.getOnboardingDate().getTime()));
        pstmt2.executeUpdate();
        ResultSet rs2 = pstmt2.getGeneratedKeys();
        int employeeId = 0;
        if (rs2.next()) {
            employeeId = rs2.getInt(1);
        }

        // 向user_employee表中插入数据
        String sql3 = "INSERT INTO user_employee (user_id, employee_id) VALUES (?, ?)";
        PreparedStatement pstmt3 = conn.prepareStatement(sql3);
        pstmt3.setInt(1, userId);
        pstmt3.setInt(2, employeeId);
        pstmt3.executeUpdate();


        // 向user_role表中插入数据
        String sql5 = "INSERT INTO user_role (user_id,role_id) VALUES (?, ?)";
        PreparedStatement pstmt5 = conn.prepareStatement(sql5);
        int[] roleIds = {4, 6}; // 定义要插入的角色ID数组
        for (int roleId : roleIds) {
            pstmt5.setInt(1, userId);
            pstmt5.setInt(2, roleId);
            pstmt5.executeUpdate();
        }

        DruidUtils.close(conn);

    }

    @Override
    public List<Employee> findAll() throws SQLException {
        Connection connection = DruidUtils.getConnection();

        PreparedStatement ps = connection.prepareStatement("SELECT * FROM employee");

        ResultSet rs = ps.executeQuery();

        List<Employee> list = new ArrayList<>();

        while (rs.next()) {
            Employee employee = new Employee();

            employee.setEmployeeId(rs.getInt("employee_id"));
            ////employee.setName(rs.getString("name"));
            //employee.setAccount(rs.getString("account"));
            //employee.setGender(rs.getString("gender"));
            //employee.setBorndate(rs.getDate("born_date"));
            //employee.setPassword(rs.getString("password"));
            //employee.setPhone(rs.getString("phone"));
            employee.setImageAddress(rs.getString("image_address"));
            employee.setEmail(rs.getString("email"));
            employee.setStatus(rs.getInt("status"));

            list.add(employee);
        }

        DruidUtils.close(connection);

        return list;
    }

    @Override
    public Employee findById(Integer userId) throws SQLException {
        Connection connection = DruidUtils.getConnection();
        String sql = """
            SELECT
                u.user_id,
                u.user_number,
                u.user_name,
                u.english_name,
                u.login_account,
                u.gender,
                u.home_address,
                u.phone,
                u.email,
                u.borndate,
                u.image_address,
                u.company_address,
                u.pay_password,
                u.balance,
                u.pay_limit,
                u.status,
                e.employee_account,
                e.employee_id,
                e.onboarding_date,
                e.resign_date,
                r.role_name,
                r.role_id,
                r.role_desc,
                p.permission_name,
                p.permission_id,
                p.permission_desc
            FROM user u
            JOIN user_employee ue ON u.user_id = ue.user_id
            JOIN employee e ON ue.employee_id = e.employee_id
            JOIN user_role ur ON u.user_id = ur.user_id
            JOIN role r ON ur.role_id = r.role_id
            JOIN role_permission rp ON r.role_id = rp.role_id
            JOIN permission p ON rp.permission_id = p.permission_id
            WHERE u.user_id = ?;
            """;
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, userId);
        ResultSet rs = ps.executeQuery();
        Employee employee = null;
        Map<Integer, Role> roleMap = new HashMap<>(); // 创建一个空的Map集合，用于存储角色信息
        while (rs.next()) {
            if (employee == null) {
                employee = new Employee();
                employee.setUserId(rs.getInt("user_id"));
                employee.setUserNumber(rs.getString("user_number"));
                employee.setUserName(rs.getString("user_name"));
                employee.setEnglishName(rs.getString("english_name"));
                employee.setLoginAccount(rs.getString("login_account"));
                employee.setGender(rs.getString("gender"));
                employee.setEmployeeId(rs.getInt("employee_id"));
                employee.setHomeAddress(rs.getString("home_address"));
                employee.setPhone(rs.getString("phone"));
                employee.setEmail(rs.getString("email"));
                employee.setBorndate(rs.getDate("borndate"));
                employee.setImageAddress(rs.getString("image_address"));
                employee.setCompanyAddress(rs.getString("company_address"));
                employee.setPayPassword(rs.getString("pay_password"));
                employee.setBalance(rs.getDouble("balance"));
                employee.setPayLimit(rs.getDouble("pay_limit"));
                employee.setStatus(rs.getInt("status"));
                employee.setEmployeeAccount(rs.getString("employee_account"));
                employee.setOnboardingDate(rs.getDate("onboarding_date"));
                employee.setResignDate(rs.getDate("resign_date"));
            }
            int roleId = rs.getInt("role_id");
            if (!roleMap.containsKey(roleId)) { // 如果Map集合中不存在该角色ID，则创建一个新的角色对象，并将其添加到Map集合中
                Role role = new Role();
                role.setRoleId(roleId);
                role.setRoleName(rs.getString("role_name"));
                role.setRoleDesc(rs.getString("role_desc"));
                List<Permission> permissions = new ArrayList<>(); // 创建一个空的集合，用于存储角色的所有权限信息
                Permission permission = new Permission();
                permission.setPermissionName(rs.getString("permission_name"));
                permission.setPermissionId(rs.getInt("permission_id"));
                permission.setPermissionDesc(rs.getString("permission_desc"));
                permissions.add(permission); // 将权限信息添加到集合中
                role.setPermissions(permissions); // 将所有权限信息设置到角色对象中
                roleMap.put(roleId, role); // 将角色对象添加到Map集合中
            } else { // 如果Map集合中已经存在该角色ID，则将权限信息添加到该角色对象中
                Role role = roleMap.get(roleId);
                List<Permission> permissions = role.getPermissions(); // 获取角色的权限信息
                Permission permission = new Permission();
                permission.setPermissionName(rs.getString("permission_name"));
                permission.setPermissionId(rs.getInt("permission_id"));
                permission.setPermissionDesc(rs.getString("permission_desc"));
                permissions.add(permission); // 将权限信息添加到集合中
                role.setPermissions(permissions); // 将所有权限信息设置到角色对象中
            }
        }
        if (employee != null) {
            List<Role> roles = new ArrayList<>(roleMap.values()); // 将Map集合中的所有角色对象转换为List集合
            employee.setRoles(roles); // 将所有角色信息设置到员工对象中
        }
        DruidUtils.close(connection);

        return employee;
    }

    @Override
    public void updateRole(Integer employeeId, Integer userId, List<Integer> roles) throws SQLException {
        Connection connection = DruidUtils.getConnection();
        PreparedStatement ps = null;

        String sql = "DELETE FROM user_role WHERE user_id = ?";
        ps = connection.prepareStatement(sql);
        ps.setInt(1, userId);
        ps.executeUpdate();

        String sql1 = "INSERT INTO user_role (user_id, role_id) VALUES (?, ?)";
        ps = connection.prepareStatement(sql1);
        for (Integer role : roles) {
            ps.setInt(1, userId);
            ps.setInt(2, role);
            ps.executeUpdate();
        }

        String sql2;
        if (!roles.contains(5)) {
            sql2 = "UPDATE employee SET resign_date = NULL WHERE employee_id = ?";
        } else {
            sql2 = "UPDATE employee SET resign_date = now() WHERE employee_id = ?";
        }
        ps = connection.prepareStatement(sql2);
        ps.setInt(1, employeeId);
        ps.executeUpdate();

        DruidUtils.close(connection);
    }

    @Override
    public Employee findByLoginAccount(String loginAccount) throws SQLException {
        Connection connection = DruidUtils.getConnection();
        String sql = """
        SELECT
            u.user_id,
            u.user_number,
            u.user_name,
            u.english_name,
            u.login_account,
            u.gender,
            u.home_address,
            u.phone,
            u.email,
            u.borndate,
            u.image_address,
            u.company_address,
            u.pay_password,
            u.balance,
            u.pay_limit,
            u.status,
            e.employee_account,
            e.onboarding_date,
            e.resign_date,
            r.role_name,
            r.role_id,
            r.role_desc,
            p.permission_name
        FROM user u
        JOIN user_employee ue ON u.user_id = ue.user_id
        JOIN employee e ON ue.employee_id = e.employee_id
        JOIN user_role ur ON u.user_id = ur.user_id
        JOIN role r ON ur.role_id = r.role_id
        JOIN role_permission rp ON r.role_id = rp.role_id
        JOIN permission p ON rp.permission_id = p.permission_id
        WHERE u.login_account = ?;
        """;
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, loginAccount);
        ResultSet rs = ps.executeQuery();
        Employee employee = null;
        Map<Integer, Role> roleMap = new HashMap<>(); // 创建一个空的Map集合，用于存储角色信息
        while (rs.next()) {
            if (employee == null) {
                employee = new Employee();
                employee.setUserId(rs.getInt("user_id"));
                employee.setUserNumber(rs.getString("user_number"));
                employee.setUserName(rs.getString("user_name"));
                employee.setEnglishName(rs.getString("english_name"));
                employee.setLoginAccount(rs.getString("login_account"));
                employee.setGender(rs.getString("gender"));
                employee.setHomeAddress(rs.getString("home_address"));
                employee.setPhone(rs.getString("phone"));
                employee.setEmail(rs.getString("email"));
                employee.setBorndate(rs.getDate("borndate"));
                employee.setImageAddress(rs.getString("image_address"));
                employee.setCompanyAddress(rs.getString("company_address"));
                employee.setPayPassword(rs.getString("pay_password"));
                employee.setBalance(rs.getDouble("balance"));
                employee.setPayLimit(rs.getDouble("pay_limit"));
                employee.setStatus(rs.getInt("status"));
                employee.setEmployeeAccount(rs.getString("employee_account"));
                employee.setOnboardingDate(rs.getDate("onboarding_date"));
                employee.setResignDate(rs.getDate("resign_date"));
            }
            int roleId = rs.getInt("role_id");
            if (!roleMap.containsKey(roleId)) { // 如果Map集合中不存在该角色ID，则创建一个新的角色对象，并将其添加到Map集合中
                Role role = new Role();
                role.setRoleId(roleId);
                role.setRoleName(rs.getString("role_name"));
                List<Permission> permissions = new ArrayList<>(); // 创建一个空的集合，用于存储角色的所有权限信息
                Permission permission = new Permission();
                permission.setPermissionName(rs.getString("permission_name"));
                permissions.add(permission); // 将权限信息添加到集合中
                role.setPermissions(permissions); // 将所有权限信息设置到角色对象中
                roleMap.put(roleId, role); // 将角色对象添加到Map集合中
            } else { // 如果Map集合中已经存在该角色ID，则将权限信息添加到该角色对象中
                Role role = roleMap.get(roleId);
                List<Permission> permissions = role.getPermissions(); // 获取角色的权限信息
                Permission permission = new Permission();
                permission.setPermissionName(rs.getString("permission_name"));
                permissions.add(permission); // 将权限信息添加到集合中
                role.setPermissions(permissions); // 将所有权限信息设置到角色对象中
            }
        }
        if (employee != null) {
            List<Role> roles = new ArrayList<>(roleMap.values()); // 将Map集合中的所有角色对象转换为List集合
            employee.setRoles(roles); // 将所有角色信息设置到员工对象中
        }
        DruidUtils.close(connection);
        return employee;
    }
}
